"""
Excel输出生成器

负责生成项目资源规划的Excel输出文件，包括：
1. 山積み表（资源累积表）
2. リソースヒストグラム（资源直方图）
3. 项目汇总表
4. 资源利用率分析表
"""

import pandas as pd
import openpyxl
from openpyxl.styles import Font, PatternFill, Border, Side, Alignment
from openpyxl.chart import BarChart, LineChart, Reference
from openpyxl.utils.dataframe import dataframe_to_rows
from typing import List, Dict, Any, Optional, Tuple
from pathlib import Path
import logging
from datetime import datetime
from collections import defaultdict

from ..models.data_models import ProjectInfo, ProjectRole, PhaseDetail, MonthlyAllocation


class ExcelGenerator:
    """
    Excel输出生成器
    
    基于模板或从零开始生成包含资源分配结果的Excel文件。
    """
    
    def __init__(self):
        """初始化Excel生成器"""
        self.logger = logging.getLogger(__name__)
        
        # 样式定义
        self.styles = {
            'header': {
                'font': Font(bold=True, color='FFFFFF'),
                'fill': PatternFill(start_color='4472C4', end_color='4472C4', fill_type='solid'),
                'alignment': Alignment(horizontal='center', vertical='center')
            },
            'subheader': {
                'font': Font(bold=True),
                'fill': PatternFill(start_color='D9E2F3', end_color='D9E2F3', fill_type='solid'),
                'alignment': Alignment(horizontal='center', vertical='center')
            },
            'data': {
                'alignment': Alignment(horizontal='center', vertical='center')
            },
            'total': {
                'font': Font(bold=True),
                'fill': PatternFill(start_color='E2EFDA', end_color='E2EFDA', fill_type='solid'),
                'alignment': Alignment(horizontal='center', vertical='center')
            }
        }
        
        # 边框样式
        self.border = Border(
            left=Side(style='thin'),
            right=Side(style='thin'),
            top=Side(style='thin'),
            bottom=Side(style='thin')
        )
    
    def generate_resource_plan_excel(
        self,
        allocations: List[MonthlyAllocation],
        projects: List[ProjectInfo],
        project_roles: List[ProjectRole],
        phases: List[PhaseDetail],
        output_path: str,
        template_path: Optional[str] = None
    ) -> None:
        """
        生成资源规划Excel文件
        
        Args:
            allocations: 月度分配结果列表
            projects: 项目信息列表
            project_roles: 项目角色列表
            phases: 阶段详细信息列表
            output_path: 输出文件路径
            template_path: 可选的模板文件路径
        """
        try:
            self.logger.info(f"开始生成资源规划Excel文件: {output_path}")
            
            # 如果提供了模板，则基于模板生成；否则从零开始
            if template_path and Path(template_path).exists():
                workbook = openpyxl.load_workbook(template_path)
                self.logger.info(f"使用模板文件: {template_path}")
            else:
                workbook = openpyxl.Workbook()
                # 删除默认的工作表
                if 'Sheet' in workbook.sheetnames:
                    workbook.remove(workbook['Sheet'])
                self.logger.info("从零开始创建Excel文件")
            
            # 生成各个工作表
            self._generate_resource_accumulation_sheet(workbook, allocations, projects)
            self._generate_resource_histogram_sheet(workbook, allocations, projects)
            self._generate_project_summary_sheet(workbook, allocations, projects, project_roles, phases)
            self._generate_utilization_analysis_sheet(workbook, allocations, projects)
            self._generate_monthly_detail_sheet(workbook, allocations, projects)
            
            # 保存文件
            workbook.save(output_path)
            self.logger.info(f"Excel文件生成完成: {output_path}")
            
        except Exception as e:
            self.logger.error(f"生成Excel文件失败: {e}")
            raise
    
    def _generate_resource_accumulation_sheet(
        self,
        workbook: openpyxl.Workbook,
        allocations: List[MonthlyAllocation],
        projects: List[ProjectInfo]
    ) -> None:
        """
        生成资源累积表（山積み表）
        
        Args:
            workbook: Excel工作簿
            allocations: 月度分配结果
            projects: 项目信息列表
        """
        sheet_name = '山積み表'
        if sheet_name in workbook.sheetnames:
            ws = workbook[sheet_name]
            ws.delete_rows(1, ws.max_row)
        else:
            ws = workbook.create_sheet(sheet_name)
        
        # 获取所有月份和角色
        all_months = sorted(set(a.month for a in allocations))
        all_roles = sorted(set(a.role_name for a in allocations))
        
        if not all_months or not all_roles:
            self.logger.warning("没有数据可生成山積み表")
            return
        
        # 构建数据结构：{role: {month: total_count}}
        role_monthly_totals = defaultdict(lambda: defaultdict(int))
        
        for allocation in allocations:
            role_monthly_totals[allocation.role_name][allocation.month] += allocation.allocated_count
        
        # 写入标题
        ws['A1'] = '资源累积表（山積み表）'
        ws.merge_cells('A1:' + openpyxl.utils.get_column_letter(len(all_months) + 1) + '1')
        self._apply_style(ws['A1'], self.styles['header'])
        
        # 写入表头
        ws['A2'] = '角色/月份'
        self._apply_style(ws['A2'], self.styles['subheader'])
        
        for col_idx, month in enumerate(all_months, start=2):
            cell = ws.cell(row=2, column=col_idx, value=month)
            self._apply_style(cell, self.styles['subheader'])
        
        # 写入数据
        current_row = 3
        accumulated_data = defaultdict(lambda: defaultdict(int))  # 存储累积数据
        
        for role_idx, role in enumerate(all_roles):
            ws.cell(row=current_row, column=1, value=role)
            self._apply_style(ws.cell(row=current_row, column=1), self.styles['subheader'])
            
            for col_idx, month in enumerate(all_months, start=2):
                current_count = role_monthly_totals[role][month]
                
                # 计算累积值（当前月份 + 前面所有月份的和）
                if col_idx == 2:  # 第一个月
                    accumulated_count = current_count
                else:
                    prev_month_idx = col_idx - 1
                    prev_accumulated = accumulated_data[role][all_months[prev_month_idx - 2]]
                    accumulated_count = prev_accumulated + current_count
                
                accumulated_data[role][month] = accumulated_count
                
                cell = ws.cell(row=current_row, column=col_idx, value=accumulated_count)
                self._apply_style(cell, self.styles['data'])
            
            current_row += 1
        
        # 添加总计行
        ws.cell(row=current_row, column=1, value='总计')
        self._apply_style(ws.cell(row=current_row, column=1), self.styles['total'])
        
        for col_idx, month in enumerate(all_months, start=2):
            total_accumulated = sum(accumulated_data[role][month] for role in all_roles)
            cell = ws.cell(row=current_row, column=col_idx, value=total_accumulated)
            self._apply_style(cell, self.styles['total'])
        
        # 应用边框
        self._apply_borders(ws, 1, 1, current_row, len(all_months) + 1)
        
        # 自动调整列宽
        self._auto_adjust_columns(ws)
    
    def _generate_resource_histogram_sheet(
        self,
        workbook: openpyxl.Workbook,
        allocations: List[MonthlyAllocation],
        projects: List[ProjectInfo]
    ) -> None:
        """
        生成资源直方图（リソースヒストグラム）
        
        Args:
            workbook: Excel工作簿
            allocations: 月度分配结果
            projects: 项目信息列表
        """
        sheet_name = 'リソースヒストグラム'
        if sheet_name in workbook.sheetnames:
            ws = workbook[sheet_name]
            ws.delete_rows(1, ws.max_row)
        else:
            ws = workbook.create_sheet(sheet_name)
        
        # 获取所有月份和角色
        all_months = sorted(set(a.month for a in allocations))
        all_roles = sorted(set(a.role_name for a in allocations))
        
        if not all_months or not all_roles:
            self.logger.warning("没有数据可生成リソースヒストグラム")
            return
        
        # 构建数据结构：{role: {month: count}}
        role_monthly_data = defaultdict(lambda: defaultdict(int))
        
        for allocation in allocations:
            role_monthly_data[allocation.role_name][allocation.month] += allocation.allocated_count
        
        # 写入标题
        ws['A1'] = '资源直方图（リソースヒストグラム）'
        ws.merge_cells('A1:' + openpyxl.utils.get_column_letter(len(all_months) + 1) + '1')
        self._apply_style(ws['A1'], self.styles['header'])
        
        # 写入表头
        ws['A2'] = '角色/月份'
        self._apply_style(ws['A2'], self.styles['subheader'])
        
        for col_idx, month in enumerate(all_months, start=2):
            cell = ws.cell(row=2, column=col_idx, value=month)
            self._apply_style(cell, self.styles['subheader'])
        
        # 写入数据
        current_row = 3
        
        for role in all_roles:
            ws.cell(row=current_row, column=1, value=role)
            self._apply_style(ws.cell(row=current_row, column=1), self.styles['subheader'])
            
            for col_idx, month in enumerate(all_months, start=2):
                count = role_monthly_data[role][month]
                cell = ws.cell(row=current_row, column=col_idx, value=count)
                self._apply_style(cell, self.styles['data'])
            
            current_row += 1
        
        # 添加总计行
        ws.cell(row=current_row, column=1, value='总计')
        self._apply_style(ws.cell(row=current_row, column=1), self.styles['total'])
        
        for col_idx, month in enumerate(all_months, start=2):
            total_count = sum(role_monthly_data[role][month] for role in all_roles)
            cell = ws.cell(row=current_row, column=col_idx, value=total_count)
            self._apply_style(cell, self.styles['total'])
        
        # 应用边框
        self._apply_borders(ws, 1, 1, current_row, len(all_months) + 1)
        
        # 创建图表
        self._create_histogram_chart(ws, all_roles, all_months, current_row + 2)
        
        # 自动调整列宽
        self._auto_adjust_columns(ws)
    
    def _generate_project_summary_sheet(
        self,
        workbook: openpyxl.Workbook,
        allocations: List[MonthlyAllocation],
        projects: List[ProjectInfo],
        project_roles: List[ProjectRole],
        phases: List[PhaseDetail]
    ) -> None:
        """
        生成项目汇总表
        
        Args:
            workbook: Excel工作簿
            allocations: 月度分配结果
            projects: 项目信息列表
            project_roles: 项目角色列表
            phases: 阶段详细信息列表
        """
        sheet_name = '项目汇总'
        if sheet_name in workbook.sheetnames:
            ws = workbook[sheet_name]
            ws.delete_rows(1, ws.max_row)
        else:
            ws = workbook.create_sheet(sheet_name)
        
        # 写入标题
        ws['A1'] = '项目资源汇总表'
        ws.merge_cells('A1:H1')
        self._apply_style(ws['A1'], self.styles['header'])
        
        # 写入表头
        headers = ['项目代码', '项目名称', '开始月份', '结束月份', '优先级', '总人月数', '阶段数量', '角色数量']
        for col_idx, header in enumerate(headers, start=1):
            cell = ws.cell(row=2, column=col_idx, value=header)
            self._apply_style(cell, self.styles['subheader'])
        
        # 计算每个项目的汇总数据
        project_summaries = self._calculate_project_summaries(allocations, projects, project_roles, phases)
        
        # 写入数据
        current_row = 3
        for project_code, summary in project_summaries.items():
            project = next((p for p in projects if p.project_code == project_code), None)
            if not project:
                continue
            
            ws.cell(row=current_row, column=1, value=project.project_code)
            ws.cell(row=current_row, column=2, value=project.project_name)
            ws.cell(row=current_row, column=3, value=project.start_month)
            ws.cell(row=current_row, column=4, value=project.end_month)
            ws.cell(row=current_row, column=5, value=project.priority)
            ws.cell(row=current_row, column=6, value=summary['total_person_months'])
            ws.cell(row=current_row, column=7, value=summary['phase_count'])
            ws.cell(row=current_row, column=8, value=summary['role_count'])
            
            # 应用样式
            for col_idx in range(1, 9):
                self._apply_style(ws.cell(row=current_row, column=col_idx), self.styles['data'])
            
            current_row += 1
        
        # 应用边框
        self._apply_borders(ws, 1, 1, current_row - 1, 8)
        
        # 自动调整列宽
        self._auto_adjust_columns(ws)
    
    def _generate_utilization_analysis_sheet(
        self,
        workbook: openpyxl.Workbook,
        allocations: List[MonthlyAllocation],
        projects: List[ProjectInfo]
    ) -> None:
        """
        生成资源利用率分析表
        
        Args:
            workbook: Excel工作簿
            allocations: 月度分配结果
            projects: 项目信息列表
        """
        sheet_name = '利用率分析'
        if sheet_name in workbook.sheetnames:
            ws = workbook[sheet_name]
            ws.delete_rows(1, ws.max_row)
        else:
            ws = workbook.create_sheet(sheet_name)
        
        # 写入标题
        ws['A1'] = '资源利用率分析表'
        ws.merge_cells('A1:F1')
        self._apply_style(ws['A1'], self.styles['header'])
        
        # 写入表头
        headers = ['角色名称', '总需求人月', '总分配人月', '利用率(%)', '缺口人月', '状态']
        for col_idx, header in enumerate(headers, start=1):
            cell = ws.cell(row=2, column=col_idx, value=header)
            self._apply_style(cell, self.styles['subheader'])
        
        # 计算利用率数据
        utilization_data = self._calculate_utilization_data(allocations)
        
        # 写入数据
        current_row = 3
        for role_name, data in utilization_data.items():
            ws.cell(row=current_row, column=1, value=role_name)
            ws.cell(row=current_row, column=2, value=data['required'])
            ws.cell(row=current_row, column=3, value=data['allocated'])
            ws.cell(row=current_row, column=4, value=round(data['utilization'] * 100, 1))
            ws.cell(row=current_row, column=5, value=data['shortage'])
            ws.cell(row=current_row, column=6, value=data['status'])
            
            # 应用样式
            for col_idx in range(1, 7):
                self._apply_style(ws.cell(row=current_row, column=col_idx), self.styles['data'])
            
            current_row += 1
        
        # 应用边框
        self._apply_borders(ws, 1, 1, current_row - 1, 6)
        
        # 自动调整列宽
        self._auto_adjust_columns(ws)
    
    def _generate_monthly_detail_sheet(
        self,
        workbook: openpyxl.Workbook,
        allocations: List[MonthlyAllocation],
        projects: List[ProjectInfo]
    ) -> None:
        """
        生成月度详细分配表
        
        Args:
            workbook: Excel工作簿
            allocations: 月度分配结果
            projects: 项目信息列表
        """
        sheet_name = '月度详细分配'
        if sheet_name in workbook.sheetnames:
            ws = workbook[sheet_name]
            ws.delete_rows(1, ws.max_row)
        else:
            ws = workbook.create_sheet(sheet_name)
        
        # 写入标题
        ws['A1'] = '月度详细分配表'
        ws.merge_cells('A1:H1')
        self._apply_style(ws['A1'], self.styles['header'])
        
        # 写入表头
        headers = ['月份', '项目代码', '项目名称', '角色名称', '需求人数', '分配人数', '利用率(%)', '状态']
        for col_idx, header in enumerate(headers, start=1):
            cell = ws.cell(row=2, column=col_idx, value=header)
            self._apply_style(cell, self.styles['subheader'])
        
        # 构建项目名称映射
        project_name_map = {p.project_code: p.project_name for p in projects}
        
        # 按月份和项目排序
        sorted_allocations = sorted(allocations, key=lambda a: (a.month, a.project_code, a.role_name))
        
        # 写入数据
        current_row = 3
        for allocation in sorted_allocations:
            project_name = project_name_map.get(allocation.project_code, allocation.project_code)
            utilization = (allocation.allocated_count / allocation.required_count * 100) if allocation.required_count > 0 else 0
            status = '满足' if allocation.is_satisfied else '缺口'
            
            ws.cell(row=current_row, column=1, value=allocation.month)
            ws.cell(row=current_row, column=2, value=allocation.project_code)
            ws.cell(row=current_row, column=3, value=project_name)
            ws.cell(row=current_row, column=4, value=allocation.role_name)
            ws.cell(row=current_row, column=5, value=allocation.required_count)
            ws.cell(row=current_row, column=6, value=allocation.allocated_count)
            ws.cell(row=current_row, column=7, value=round(utilization, 1))
            ws.cell(row=current_row, column=8, value=status)
            
            # 应用样式
            for col_idx in range(1, 9):
                self._apply_style(ws.cell(row=current_row, column=col_idx), self.styles['data'])
            
            current_row += 1
        
        # 应用边框
        self._apply_borders(ws, 1, 1, current_row - 1, 8)
        
        # 自动调整列宽
        self._auto_adjust_columns(ws)
    
    def _calculate_project_summaries(
        self,
        allocations: List[MonthlyAllocation],
        projects: List[ProjectInfo],
        project_roles: List[ProjectRole],
        phases: List[PhaseDetail]
    ) -> Dict[str, Dict[str, Any]]:
        """
        计算项目汇总数据
        
        Args:
            allocations: 月度分配结果
            projects: 项目信息列表
            project_roles: 项目角色列表
            phases: 阶段详细信息列表
            
        Returns:
            Dict[str, Dict[str, Any]]: 项目汇总数据
        """
        summaries = {}
        
        for project in projects:
            project_allocations = [a for a in allocations if a.project_code == project.project_code]
            project_phases = [p for p in phases if p.project_code == project.project_code]
            project_project_roles = [r for r in project_roles if r.project_code == project.project_code]
            
            total_person_months = sum(a.allocated_count for a in project_allocations)
            phase_count = len(set(p.phase_name for p in project_phases))
            role_count = len(set(r.role_name for r in project_project_roles))
            
            summaries[project.project_code] = {
                'total_person_months': total_person_months,
                'phase_count': phase_count,
                'role_count': role_count
            }
        
        return summaries
    
    def _calculate_utilization_data(self, allocations: List[MonthlyAllocation]) -> Dict[str, Dict[str, Any]]:
        """
        计算资源利用率数据
        
        Args:
            allocations: 月度分配结果
            
        Returns:
            Dict[str, Dict[str, Any]]: 利用率数据
        """
        role_data = defaultdict(lambda: {'required': 0, 'allocated': 0})
        
        for allocation in allocations:
            role_data[allocation.role_name]['required'] += allocation.required_count
            role_data[allocation.role_name]['allocated'] += allocation.allocated_count
        
        utilization_data = {}
        for role_name, data in role_data.items():
            required = data['required']
            allocated = data['allocated']
            utilization = allocated / required if required > 0 else 0
            shortage = max(0, required - allocated)
            status = '充足' if shortage == 0 else '缺口'
            
            utilization_data[role_name] = {
                'required': required,
                'allocated': allocated,
                'utilization': utilization,
                'shortage': shortage,
                'status': status
            }
        
        return utilization_data
    
    def _create_histogram_chart(
        self,
        worksheet: openpyxl.worksheet.worksheet.Worksheet,
        roles: List[str],
        months: List[str],
        start_row: int
    ) -> None:
        """
        创建资源直方图图表
        
        Args:
            worksheet: 工作表对象
            roles: 角色列表
            months: 月份列表
            start_row: 图表起始行
        """
        try:
            # 创建柱状图
            chart = BarChart()
            chart.type = "col"
            chart.style = 10
            chart.title = "资源分配直方图"
            chart.y_axis.title = '人数'
            chart.x_axis.title = '月份'
            
            # 设置数据范围
            data_ref = Reference(worksheet, min_col=2, min_row=2, max_col=len(months) + 1, max_row=len(roles) + 2)
            categories_ref = Reference(worksheet, min_col=2, min_row=2, max_col=len(months) + 1, max_row=2)
            
            chart.add_data(data_ref, titles_from_data=True)
            chart.set_categories(categories_ref)
            
            # 设置图表位置
            chart.anchor = f"A{start_row}"
            chart.width = 15
            chart.height = 10
            
            worksheet.add_chart(chart)
            
        except Exception as e:
            self.logger.warning(f"创建图表失败: {e}")
    
    def _apply_style(self, cell: openpyxl.cell.cell.Cell, style: Dict[str, Any]) -> None:
        """
        应用样式到单元格
        
        Args:
            cell: 单元格对象
            style: 样式字典
        """
        if 'font' in style:
            cell.font = style['font']
        if 'fill' in style:
            cell.fill = style['fill']
        if 'alignment' in style:
            cell.alignment = style['alignment']
        if 'border' in style:
            cell.border = style['border']
        
        # 默认添加边框
        cell.border = self.border
    
    def _apply_borders(
        self,
        worksheet: openpyxl.worksheet.worksheet.Worksheet,
        start_row: int,
        start_col: int,
        end_row: int,
        end_col: int
    ) -> None:
        """
        为指定区域应用边框
        
        Args:
            worksheet: 工作表对象
            start_row: 起始行
            start_col: 起始列
            end_row: 结束行
            end_col: 结束列
        """
        for row in range(start_row, end_row + 1):
            for col in range(start_col, end_col + 1):
                worksheet.cell(row=row, column=col).border = self.border
    
    def _auto_adjust_columns(self, worksheet: openpyxl.worksheet.worksheet.Worksheet) -> None:
        """
        自动调整列宽
        
        Args:
            worksheet: 工作表对象
        """
        for column in worksheet.columns:
            max_length = 0
            # 获取列字母，跳过合并单元格
            column_letter = None
            
            for cell in column:
                # 跳过合并单元格
                if hasattr(cell, 'column_letter'):
                    if column_letter is None:
                        column_letter = cell.column_letter
                    
                    try:
                        if cell.value and len(str(cell.value)) > max_length:
                            max_length = len(str(cell.value))
                    except:
                        pass
            
            # 只有在找到有效列字母时才调整宽度
            if column_letter:
                adjusted_width = min(max_length + 2, 50)  # 限制最大宽度为50
                worksheet.column_dimensions[column_letter].width = adjusted_width
    
    def export_template(self, output_path: str) -> None:
        """
        导出Excel模板文件
        
        Args:
            output_path: 输出文件路径
        """
        try:
            workbook = openpyxl.Workbook()
            
            # 删除默认工作表
            if 'Sheet' in workbook.sheetnames:
                workbook.remove(workbook['Sheet'])
            
            # 创建模板工作表
            template_sheets = ['山積み表', 'リソースヒストグラム', '项目汇总', '利用率分析', '月度详细分配']
            
            for sheet_name in template_sheets:
                ws = workbook.create_sheet(sheet_name)
                ws['A1'] = f'{sheet_name}模板'
                self._apply_style(ws['A1'], self.styles['header'])
            
            workbook.save(output_path)
            self.logger.info(f"Excel模板已导出到: {output_path}")
            
        except Exception as e:
            self.logger.error(f"导出Excel模板失败: {e}")
            raise